CREATE PROCEDURE [dbo].[selAllStaffTmp$]
AS
BEGIN
	SELECT s.StaffID, s.StaffName, s.StaffPhone, s.StaffCompanyID, c.CompanyName, s.StaffBirthday, s.StaffGender, s.StaffAddress, s.StaffEmail, s.StaffScale, s.StaffLevel, s.StaffPermissionID, s.StaffPermissionID, p.PermissionName, s.StaffResultUnitID, r.ResultUnitName, s.StaffPosition
	FROM VTK_Staff$ AS s INNER JOIN VTK_Company AS c ON s.StaffCompanyID = c.CompanyID INNER JOIN VTK_Permission$ AS p ON s.StaffPermissionID = p.PermissionID LEFT JOIN VTK_ResultUnit$ AS r ON s.StaffResultUnitID = r.ResultUnitID
END

/* *********************************************** */
CREATE PROCEDURE [dbo].[updStaffOptTmp$]
	@StaffName NVARCHAR(100),
	@StaffResultUnitID INT,
	@StaffPosition NVARCHAR(200),
	@StaffEmail VARCHAR(200),
	@StaffPermissionID INT
AS
BEGIN
	UPDATE 
		VTK_Staff$ 
	SET 
		StaffName = @StaffName,
		StaffResultUnitID = @StaffResultUnitID,
		StaffPosition = @StaffPosition,
		StaffPermissionID = @StaffPermissionID
	WHERE
		StaffEmail = @StaffEmail
END
/* *********************************************** */
CREATE PROCEDURE [dbo].[updStaffTmp$]
	@StaffName NVARCHAR(100),
	@StaffPhone VARCHAR(50),
	@StaffCompanyID INT,
	@StaffBirthday DATETIME,
	@StaffGender BIT,
	@StaffAddress NVARCHAR(200),
	@StaffEmail VARCHAR(200),
	@StaffScale NVARCHAR(50),
	@StaffLevel INT,
	@StaffPermissionID INT, 
	@StaffResultUnitID INT,
	@StaffPosition NVARCHAR(200)
AS
BEGIN
	UPDATE 
		VTK_Staff$
	SET 
		StaffName = @StaffName,
		StaffPhone = @StaffPhone,
		StaffCompanyID = @StaffCompanyID,
		StaffBirthday = @StaffBirthday,
		StaffGender = @StaffGender,
		StaffAddress = @StaffAddress,
		StaffScale = @StaffScale,
		StaffLevel = @StaffLevel,
		StaffPermissionID = @StaffPermissionID, 
		StaffResultUnitID = @StaffResultUnitID,
		StaffPosition = @StaffPosition
	WHERE
		StaffEmail = @StaffEmail
END

/* *********************************************** */
CREATE PROCEDURE [dbo].[updMyProfileTmp$]
	@StaffName NVARCHAR(100),
	@StaffPhone VARCHAR(50),
	@StaffCompanyID INT,
	@StaffBirthday DATETIME,
	@StaffGender BIT,
	@StaffAddress NVARCHAR(200),
	@StaffEmail VARCHAR(200),
	@StaffScale NVARCHAR(50),
	@StaffLevel INT,
	@StaffResultUnitID INT,
	@StaffPosition NVARCHAR(200)
AS
BEGIN
	UPDATE 
		VTK_Staff$
	SET 
		StaffName = @StaffName,
		StaffPhone = @StaffPhone,
		StaffCompanyID = @StaffCompanyID,
		StaffBirthday = @StaffBirthday,
		StaffGender = @StaffGender,
		StaffAddress = @StaffAddress,
		StaffScale = @StaffScale,
		StaffLevel = @StaffLevel,
		StaffResultUnitID = @StaffResultUnitID,
		StaffPosition = @StaffPosition
	WHERE
		StaffEmail = @StaffEmail
END
/* *********************************************** */
CREATE PROCEDURE [dbo].[insStaffTmp$]
	@StaffName NVARCHAR(100),
	@StaffPhone VARCHAR(50),
	@StaffCompanyID INT,
	@StaffBirthday DATETIME,
	@StaffGender BIT,
	@StaffAddress NVARCHAR(200),
	@StaffEmail VARCHAR(200),
	@StaffScale NVARCHAR(50),
	@StaffLevel INT,
	@StaffPermissionID INT,
	@StaffPassword NVARCHAR(50),
	@StaffResultUnitID INT,
	@StaffPosition NVARCHAR(200)
	
AS
BEGIN
	INSERT INTO VTK_Staff$(StaffName, StaffPhone, StaffCompanyID, StaffDepartmentID, StaffBirthday, StaffGender, StaffAddress, StaffEmail, StaffScale, StaffLevel, StaffPermissionID, StaffPassword, StaffResultUnitID, StaffPosition)
	VALUES(@StaffName, @StaffPhone, @StaffCompanyID, 1, @StaffBirthday, @StaffGender, @StaffAddress, @StaffEmail, @StaffScale, @StaffLevel, @StaffPermissionID, @StaffPassword, @StaffResultUnitID, @StaffPosition)

	INSERT INTO VTK_Login(LoginStaffEmail, LoginStaffPassword, LoginCompanyID)
	VALUES(@StaffEmail,@StaffPassword, @StaffCompanyID)
END

/* *********************************************** */
CREATE PROCEDURE [dbo].[selStaffByEmailTmp$]
	@Email varchar(100)	
AS
BEGIN
	SELECT s.StaffID, s.StaffName, s.StaffPhone, s.StaffCompanyID, c.CompanyName, s.StaffBirthday, s.StaffGender, s.StaffAddress, s.StaffEmail, s.StaffScale, s.StaffLevel, s.StaffPermissionID, s.StaffPermissionID, p.PermissionName, s.StaffResultUnitID, r.ResultUnitName, s.StaffPosition
	FROM VTK_Staff$ AS s INNER JOIN VTK_Company AS c ON s.StaffCompanyID = c.CompanyID INNER JOIN VTK_Permission$ AS p ON s.StaffPermissionID = p.PermissionID LEFT JOIN VTK_ResultUnit$ AS r ON s.StaffResultUnitID = r.ResultUnitID   
	WHERE s.StaffEmail = @Email
END
/* *********************************************** */
CREATE PROCEDURE [dbo].[selCompanyTimeRegister$]
AS
BEGIN
	SELECT ISNULL(SUM(SAReportTimeHour),0) TimeHour, ISNULL(SUM(SAReportTimeMinute),0) TimeMinute
	FROM VTK_SA$
END
/* *********************************************** */
CREATE PROCEDURE [dbo].[selNumOfStaff$]
AS
BEGIN
	SELECT ISNULL(COUNT(1),0) NumOfStaff
	FROM VTK_Staff$
END
/* *********************************************** */





CREATE TABLE [dbo].[VTK_SignIn](
	[SignInEmail] [nvarchar](200) NOT NULL,
	[SignInPassword] [nvarchar](200) NOT NULL,
	[SignInName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_VTK_SignIn] PRIMARY KEY CLUSTERED 
(
	[SignInEmail] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
/* *********************************************** */
CREATE PROCEDURE [dbo].[checkSignIn]
	@Email VARCHAR(200),
	@Password NVARCHAR(50), 
    @Result SMALLINT OUTPUT
AS
BEGIN

	DECLARE @OutPut INT
	DECLARE @CheckEmail INT
	DECLARE @CheckPass INT

	-- Kiem tra ton tai email
	SELECT @CheckEmail=ISNULL(COUNT(SignInEmail),0) 
		FROM VTK_SignIn
		WHERE SignInEmail=@Email

	IF @CheckEmail=0 
		SET @OutPut=1 -- Sai Email
	ELSE
	BEGIN
		SELECT @CheckPass =ISNULL(COUNT(*),0)
			FROM VTK_SignIn
			WHERE SignInEmail = @Email AND SignInPassword = @Password

		IF @CheckPass=0
			SET @OutPut=2 -- Sai m?t kh?u
		ELSE
			SET @Output = 0
	END

	SET @Result = @Output
END
/* *********************************************** */
CREATE PROCEDURE [dbo].[selSignIn]
	@Email VARCHAR(200)
AS
BEGIN
	SELECT SignInName
	FROM VTK_SignIn
	WHERE SignInEmail = @Email
END
/* *********************************************** */
CREATE PROCEDURE [dbo].[selCompanyRegister]
AS
BEGIN
	SELECT CompanyID, CompanyName, CompanyTrend, CompanyAddress, CompanyDescription
	FROM VTK_Company
END

/* *********************************************** */